package com.reservation.mapper;

import com.reservation.entity.Reservation;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ReservationMapper {
    
    @Select("SELECT r.*, u.username, u.real_name, v.name as venue_name "+
            "FROM reservation r " +
            "LEFT JOIN user u ON r.user_id = u.id " +
            "LEFT JOIN venue v ON r.venue_id = v.id ")
    List<Reservation> findAll();

    @Select("SELECT r.*, u.username, u.real_name, v.name as venue_name "+
            "FROM reservation r " +
            "LEFT JOIN user u ON r.user_id = u.id " +
            "LEFT JOIN venue v ON r.venue_id = v.id "+
            "WHERE user_id = #{userId}")
    List<Reservation> findByUserId(Integer userId);
    
    @Select("SELECT * FROM reservation WHERE id = #{id}")
    Reservation findById(Integer id);
    
    @Insert("INSERT INTO reservation(user_id, venue_id, start_time, end_time, purpose, status) " +
            "VALUES(#{userId}, #{venueId}, #{startTime}, #{endTime}, #{purpose}, #{status})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int save(Reservation reservation);
    
    @Update("UPDATE reservation SET user_id = #{userId}, venue_id = #{venueId}, " +
            "start_time = #{startTime}, end_time = #{endTime}, purpose = #{purpose}, " +
            "status = #{status} WHERE id = #{id}")
    int update(Reservation reservation);
    
    @Delete("DELETE FROM reservation WHERE id = #{id}")
    int deleteById(Integer id);
    
    @Update("UPDATE reservation SET status = #{status} WHERE id = #{id}")
    int updateStatus(@Param("id") Integer id, @Param("status") String status);
    
    @Select("SELECT * FROM reservation WHERE venue_id = #{venueId}")
    List<Reservation> findByVenueId(Integer venueId);
    
    @Select("SELECT * FROM reservation WHERE venue_id = #{venueId} " +
            "AND ((start_time BETWEEN #{startTime} AND #{endTime}) " +
            "OR (end_time BETWEEN #{startTime} AND #{endTime}) " +
            "OR (start_time <= #{startTime} AND end_time >= #{endTime}))")
    List<Reservation> checkTimeConflict(@Param("venueId") Integer venueId, 
                                       @Param("startTime") String startTime, 
                                       @Param("endTime") String endTime);
    
    @Select("SELECT r.*, u.username, u.real_name, v.name as venue_name " +
            "FROM reservation r " +
            "LEFT JOIN user u ON r.user_id = u.id " +
            "LEFT JOIN venue v ON r.venue_id = v.id " +
            "WHERE (u.username LIKE CONCAT('%', #{keyword}, '%') " +
            "OR u.real_name LIKE CONCAT('%', #{keyword}, '%') " +
            "OR v.name LIKE CONCAT('%', #{keyword}, '%') " +
            "OR r.purpose LIKE CONCAT('%', #{keyword}, '%')) " +
            "AND (#{status} = '' OR r.status = #{status})")
    List<Reservation> search(@Param("keyword") String keyword, @Param("status") String status);
} 